package com.csjy.migration.hnnd.business;

import com.csjy.migration.hnnd.BusinessTool;
import com.csjy.migration.hnnd.DbUtil;

import javax.script.ScriptException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

/**
 * 项目相关源数据
 */
public class SourceBusinessData {

  private DbUtil sourceDbUtil;
  private String projectId;

  private List<Map> wfFileObjList;
  private List<Map> btItemBasicDatumList;
  private List<Map> btItemDatumSubmitList;
  private List<Map> btEnterpriseInquiryDatumList;
  private List<Map> btItemAssureSecurityList;
  private List<Map> btPersonalVerifyResultList;
  private List<Map> btItemAccountDetailList;
  private List<Map> wfAffixNotionObj;
  private List<Map> jwframeAuditConclusionList;
  private List<Map> btIemeIndentifyResult;
  private List<Map> btFlowFundItemExtent;
  private List<Map> btItemBasic;
  private List<Map> btItemFareDetail;
  private List<Map> btItemChargeAuditList;
  private List<Map> btItemInvoice;
  private List<Map> btEnterpriseVerifyResult;
  private List<Map> btItemCheckReportBasic;
  private List<Map> btItemManagerChange;
  private List<Map> btItemRateSubsidy;
  private List<Map> btItemAccountDeferRec;
  private List<Map> btItemInsteadAccountDetail;
  private List<Map> btItemDeferRec;
  private List<Map> btItemBankBailAccount;
  private List<Map> btItemIncRec;
  private List<Map> btItemArouseSecurity;
  private List<Map> btEnterpriseBasic;
  private List<Map> btEnterpriseInquiryList;
  private List<Map> jbpmTaskInstanceList;
  private List<Map> jbpmVariableInstanceList;
  private String wsbgAffixId = null;
  private List<Map> btItemAskExhgSecurityList;
  private List<Map> btItemForceRefundDetailList;
  private List<Map> btItemInsteadAuditList;

  private String repayType;

  public List<Map> getBtEnterpriseInquiryList() {
    return btEnterpriseInquiryList;
  }

  public DbUtil getSourceDbUtil() {
    return sourceDbUtil;
  }

  public String getProjectId() {
    return projectId;
  }

  public List<Map> getWfFileObjList() {
    return wfFileObjList;
  }

  public List<Map> getBtItemBasicDatumList() {
    return btItemBasicDatumList;
  }

  public List<Map> getBtItemDatumSubmitList() {
    return btItemDatumSubmitList;
  }

  public List<Map> getBtEnterpriseInquiryDatumList() {
    return btEnterpriseInquiryDatumList;
  }

  public List<Map> getBtItemAssureSecurityList() {
    return btItemAssureSecurityList;
  }

  public List<Map> getBtPersonalVerifyResultList() {
    return btPersonalVerifyResultList;
  }

  public List<Map> getBtItemAccountDetailList() {
    return btItemAccountDetailList;
  }

  public List<Map> getWfAffixNotionObj() {
    return wfAffixNotionObj;
  }

  public List<Map> getBtIemeIndentifyResult() {
    return btIemeIndentifyResult;
  }

  public List<Map> getBtFlowFundItemExtent() {
    return btFlowFundItemExtent;
  }

  public List<Map> getBtItemBasic() {
    return btItemBasic;
  }

  public List<Map> getBtItemFareDetail() {
    return btItemFareDetail;
  }

  public List<Map> getBtItemChargeAuditList() {
    return btItemChargeAuditList;
  }

  public List<Map> getBtItemInvoice() {
    return btItemInvoice;
  }

  public List<Map> getBtEnterpriseVerifyResult() {
    return btEnterpriseVerifyResult;
  }

  public List<Map> getBtItemCheckReportBasic() {
    return btItemCheckReportBasic;
  }

  public List<Map> getBtItemManagerChange() {
    return btItemManagerChange;
  }

  public List<Map> getBtItemRateSubsidy() {
    return btItemRateSubsidy;
  }

  public List<Map> getBtItemAccountDeferRec() {
    return btItemAccountDeferRec;
  }

  public List<Map> getBtItemInsteadAccountDetail() {
    return btItemInsteadAccountDetail;
  }

  public List<Map> getBtItemDeferRec() {
    return btItemDeferRec;
  }

  public List<Map> getBtItemBankBailAccount() {
    return btItemBankBailAccount;
  }

  public List<Map> getBtItemIncRec() {
    return btItemIncRec;
  }

  public List<Map> getBtItemArouseSecurity() {
    return btItemArouseSecurity;
  }

  public List<Map> getBtEnterpriseBasic() {
    return btEnterpriseBasic;
  }

  public String getWsbgAffixId() {
    return wsbgAffixId;
  }

  public List<Map> getBtItemAskExhgSecurityList() {
    return btItemAskExhgSecurityList;
  }

  public List<Map> getBtItemForceRefundDetailList() {
    return btItemForceRefundDetailList;
  }

  public List<Map> getBtItemInsteadAuditList() {
    return btItemInsteadAuditList;
  }

  public List<Map> getJbpmTaskInstanceList() {
    return jbpmTaskInstanceList;
  }

  public List<Map> getJbpmVariableInstanceList() {
    return jbpmVariableInstanceList;
  }

  public SourceBusinessData(DbUtil sourceDbUtil, String projectId) throws Exception {
    this.sourceDbUtil = sourceDbUtil;
    this.projectId = projectId;
    init();
  }

  /**
   * 初始化
   */
  public void init() throws SQLException, ScriptException {
    List values = new ArrayList<>();
    values.add(this.projectId);

    /*项目信息基础表*/
    this.btItemBasic = this.sourceDbUtil.selectByParams("SELECT \n" +
            "\tbibe.*,\n" +
            "\t(\n" +
            "\t\tCASE \n" +
            "\t\t\tWHEN a.latest_create_time = bibe.create_time THEN 1\n" +
            "\t\t\tELSE 0\n" +
            "\t\tEND\n" +
            "\t) is_latest\n" +
            "FROM  bt_item_basic bibe\n" +
            "JOIN (\n" +
            "\tSELECT min(bib.CREATE_TIME) latest_create_time,max(bib.ENTERPRISENO) enterpriseno\n" +
            "\tFROM BT_ITEM_BASIC bib \n" +
            "\tWHERE bib.ENTERPRISENO = (SELECT max(bib2.ENTERPRISENO) FROM BT_ITEM_BASIC bib2 WHERE bib2.ITEMNO  = ?)\n" +
            "\tAND bib.PRODUCECLASS = (SELECT max(bib3.PRODUCECLASS) FROM  BT_ITEM_BASIC bib3 WHERE bib3.ITEMNO  = ?)\n" +
            ") a ON a.enterpriseno = bibe.ENTERPRISENO \n" +
            "WHERE bibe.ITEMNO = ?",Arrays.asList(new String[]{this.projectId,this.projectId,this.projectId}));
    // 流程附件管理
    this.wfFileObjList = this.sourceDbUtil.selectByParams("SELECT wfo.* FROM WF_FILE_OBJ wfo\n" +
        "LEFT JOIN BT_ITEM_WORKFLOW_REC biwr ON wfo.PROCID  = biwr.WORKFLOWNO \n" +
        "WHERE biwr.ITEMNO = ?",values);
    //归档资料管理
    this.btItemBasicDatumList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_BASIC_DATUM WHERE ITEMNO = ? ",values);
    //归档资料管理（流程使用）
    this.btItemDatumSubmitList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_DATUM_SUBMIT WHERE ITEMNO = ? ",values);
    //项目附件
    this.btEnterpriseInquiryDatumList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ENTERPRISE_INQUIRY_DATUM WHERE INQUIRYNO \n" +
        "IN (SELECT INQUIRYNO FROM BT_ITEM_BASIC bib WHERE bib.ITEMNO  = ?) ",values);

    Optional<Map> wsbgInquiryDatumMap = this.btEnterpriseInquiryDatumList.stream()
        .filter(inquiryDatum->inquiryDatum.get("INQUIRYTYPE").toString().equals("2"))
        .max(Comparator.comparing(inquiryDatum->(Timestamp)inquiryDatum.get("MODIFY_TIME")));

    if(wsbgInquiryDatumMap.isPresent()){
      this.wsbgAffixId = wsbgInquiryDatumMap.get().get("DOCNO").toString();
    }

    //反担保措施表基本表
    this.btItemAssureSecurityList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_ASSURE_SECURITY WHERE ITEMNO = ? ",values);
    //关联自然人表（个人）
//    this.btPersonalVerifyResultList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_PERSONAL_VERIFY_RESULT WHERE ITEMNO = ? ",values);
    //项目台账管理( 担保责任放款,还款记录表)
    this.btItemAccountDetailList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_ACCOUNT_DETAIL WHERE ITEMNO = ? ",values);
    //流程环节审批意见
    this.wfAffixNotionObj = this.sourceDbUtil.selectByParams("SELECT o.*,c.WFNAME,c.WFDISPLAYNAME,c.ITEMNO from WF_AFFIX_NOTION_OBJ o JOIN BT_ITEM_WORKFLOW_REC c on o.procid=c.workflowno WHERE c.ITEMNO = ?",values);
    //评审结论
    String[] wfDisplayNameArr = new String[]{"调查报告评审", "项目展期评审", "项目增量评审", "项目复议评审", "项目续议评审", "更替换反担保", "更换担保措施"};
    List<String> wfDisplayNameList = Arrays.asList(wfDisplayNameArr);
    List<Map> wfAffixNotionObjList = new ArrayList<>();
    for(int i = 0;i<this.getWfAffixNotionObj().size();i++) {
      Map wfAffixNotionObj = this.getWfAffixNotionObj().get(i);
      if(wfAffixNotionObj.get("WKFLAG").toString().equals("1")
          && wfDisplayNameList.contains(wfAffixNotionObj.get("WFDISPLAYNAME").toString())){
        wfAffixNotionObjList.add(wfAffixNotionObj);
      }
    }
    Collections.sort(wfAffixNotionObjList,Comparator.comparing(a -> (Timestamp) a.get("CREATE_TIME")));
    this.jwframeAuditConclusionList = wfAffixNotionObjList;

    // 计算还款方式
    this.repayType = BusinessTool.getRepayType(this.jwframeAuditConclusionList);

    //身份验证表(企业，个人)
//    this.btIemeIndentifyResult = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_INDENTIFY_RESULT WHERE ITEMNO = ? ",values);
    //项目资产表
    this.btFlowFundItemExtent = this.sourceDbUtil.selectByParams("\n" +
        "SELECT \n" +
        "\tbffie.*,\n" +
        "\t(\n" +
        "\t\tCASE \n" +
        "\t\t\tWHEN a.latest_create_time = biba.create_time THEN 1\n" +
        "\t\t\tELSE 0\n" +
        "\t\tEND\n" +
        "\t) is_latest\n" +
        "FROM BT_FLOW_FUND_ITEM_EXTENT bffie\n" +
        "JOIN BT_ITEM_BASIC biba ON bffie.ITEMNO  = biba.ITEMNO \n" +
        "JOIN (\n" +
        "\tSELECT max(bib.CREATE_TIME) latest_create_time,max(bib.ENTERPRISENO) enterpriseno\n" +
        "\tFROM BT_ITEM_BASIC bib \n" +
        "\tWHERE bib.ENTERPRISENO = ?\n" +
        ") a ON a.enterpriseno = biba.ENTERPRISENO \n" +
        "WHERE bffie.ITEMNO = ?",Arrays.asList(new String[]{this.btItemBasic.get(0).get("ENTERPRISENO").toString(),this.projectId}));
    //客户费用收费退记录表
    this.btItemFareDetail = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_FARE_DETAIL WHERE ITEMNO = ? ",values);
    //客户费用收费退记录表 - 流程
    this.btItemChargeAuditList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_CHARGE_AUDIT WHERE ITEMNO = ? ",values);
    //收费和保证金(电子发票表)
    this.btItemInvoice = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_INVOICE_DETAIL WHERE ITEMNO = ? ",values);
    //关联法人表（法人表）
//    this.btEnterpriseVerifyResult = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ENTERPRISE_VERIFY_RESULT WHERE ITEMNO = ? ",values);
    //检查提交报告
    this.btItemCheckReportBasic = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_CHECK_REPORT_BASIC WHERE ITEMNO = ? ",values);
    //避嫌和移交记录(移交记录)
    this.btItemManagerChange = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_MANAGER_CHANGE WHERE ITEMNO = ? ",values);
    //利息补贴
    this.btItemRateSubsidy = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_RATE_SUBSIDY WHERE ITEMNO = ? ",values);
    //增量和展期记录(台账展期记录)
    this.btItemAccountDeferRec = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_ACCOUNT_DEFER_REC WHERE ITEMNO = ? ",values);
    //项目台账管理( 代偿记录表)
    this.btItemInsteadAccountDetail = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_INSTEAD_ACCOUNT_DETAIL WHERE ITEMNO = ? ",values);
    //增量和展期记录(授信展期记录)
    this.btItemDeferRec = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_DEFER_REC WHERE ITEMNO = ? ",values);
    //收费和保证金(银行保证金存退信息)
    this.btItemBankBailAccount = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_BANK_BAIL_ACCOUNT WHERE ITEMNO = ? ",values);
    //增量和展期记录(授信增量记录)
    this.btItemIncRec = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_INC_REC WHERE ITEMNO = ? ",values);
    // 避嫌和移交记录(避嫌)
    this.btItemArouseSecurity = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_AROUSE_SECURITY WHERE ITEMNO = ? ",values);
    //客户表
    this.btEnterpriseBasic = this.sourceDbUtil.selectByParams("SELECT t1.* FROM BT_ENTERPRISE_BASIC t1 LEFT JOIN BT_ITEM_BASIC t2 ON t1.ENTERPRISENO = t2.ENTERPRISENO WHERE t2.ITEMNO = ? ",values);
    //
    this.btEnterpriseInquiryList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ENTERPRISE_INQUIRY WHERE INQUIRYNO \n" +
        "IN (SELECT INQUIRYNO FROM BT_ITEM_BASIC bib WHERE bib.ITEMNO  = ?)",values);

    // 更替换反担保变化表
    this.btItemAskExhgSecurityList = this.sourceDbUtil.selectByParams("SELECT BIAES.*,bias.WAYTYPE, bias.ISTRANSACT ,bias.VAILABLETIME ,bias.PRICESUM ,bias.TRANSACTMEMO, \n" +
        "bias.OVERTIMEFLAG ,bias.OVERTIME ,bias.AUDITFLAG ,bias.OWNERADDRESS ,bias.LINKTEL \n" +
        "FROM BT_ITEM_ASK_EXHG_SECURITY biaes ,BT_ITEM_ASSURE_SECURITY bias  WHERE biaes.DATUMNO = bias.DATUMNO AND biaes.ITEMNO = ? ", values);
    // 追偿所得物清单
    this.btItemForceRefundDetailList = this.sourceDbUtil.selectByParams("SELECT * FROM bt_item_force_refund_detail WHERE ITEMNO = ? ", values);
    // 代偿流程流程记录
    this.btItemInsteadAuditList = this.sourceDbUtil.selectByParams("SELECT * FROM BT_ITEM_INSTEAD_AUDIT WHERE ITEMNO = ? ", values);
    // 项目流程任务实例
    this.jbpmTaskInstanceList = this.sourceDbUtil.selectByParams("SELECT " +
        " jt.*,jtk.NAME_ AS TASK_DEF_NAME_, jtk.PROCESSDEFINITION_ ,jp.NAME_ AS PROCDEF_NAME_," +
        " (SELECT DISTINCT fe.EMPNAME FROM FT_EMPLOYEE fe WHERE fe.TOPORGID = (" +
        "     case when SUBSTR(jt.ACTORID_ ,0, INSTR(jt.ACTORID_ ,'.')-1)='HNY' THEN 9051 ELSE 17575 END" +
        "   )" +
        "  AND fe.EMPCODE  = SUBSTR(jt.ACTORID_ ,INSTR(jt.ACTORID_ ,'.')+1)" +
        ") AS EMPNAME\n" +
        "FROM JBPM_TASKINSTANCE jt \n" +
        "JOIN JBPM_TASK jtk ON jt.TASK_ = jtk.ID_\n" +
        "JOIN JBPM_PROCESSDEFINITION jp ON jtk.PROCESSDEFINITION_ = jp.ID_\n"+
        "JOIN BT_ITEM_WORKFLOW_REC biwr ON biwr.WORKFLOWNO = jt.PROCINST_\n"+
        "WHERE biwr.ITEMNO = ? ORDER BY jt.CREATE_,jt.ISOPEN_",values);

    // 项目流程数据
    this.jbpmVariableInstanceList = this.sourceDbUtil.selectByParams(
        "SELECT jv.ID_, jv.NAME_ ,jv.STRINGVALUE_ ,jv.PROCESSINSTANCE_ ,jv.TASKINSTANCE_  \n" +
        "from JBPM_VARIABLEINSTANCE jv \n" +
        "JOIN BT_ITEM_WORKFLOW_REC biwr ON jv.PROCESSINSTANCE_  = biwr.WORKFLOWNO \n" +
        "WHERE biwr.ITEMNO = ?",values);

  }

  public List<Map> getJwframeAuditConclusionList() {
    return jwframeAuditConclusionList;
  }

  public String getRepayType() {
    return repayType;
  }
}
